/*
 * TepcoMeter:WattInfoDao.java
 * Copyright © 2011 ho9ho9 All Rights Reserved.
 */
package com.ho9ho9.tepcometer.db;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.ho9ho9.tepcometer.dto.HourDto;
import com.ho9ho9.tepcometer.dto.WattDto;

/**
 * WattInfoDaoクラス.
 * @author ho9ho9
 */
public class TepcoMeterDao {
	private static final String TAG_NAME = "TepcoMeterDao";
	
	private static final DateFormat WATT_DAY_FORMAT = new SimpleDateFormat("yyyyMMdd");
	private static final DateFormat UPDATED_FORMAT = new SimpleDateFormat("yyyyMMddHHmm");
	private static final DateFormat INSERT_DATE_FORMAT = new SimpleDateFormat("yyyyMMddHHmmssSSS");
	
	public static final class WattInfo {
		public static final String TABLE_NAME = "WATT_INFO";
		public static final String COL_WATT_DAY = "WATT_DAY";
		public static final String COL_CAPABILITY = "CAPABILITY";
		public static final String COL_UPDATED = "UPDATED";
		public static final String COL_INSERT_DATE = "INSERT_DATE";
	}
	public static final class WattHours {
		public static final String TABLE_NAME = "WATT_HOURS";
		public static final String COL_WATT_DAY = "WATT_DAY";
		public static final String COL_WATT_HOUR = "WATT_HOUR";
		public static final String COL_WATT_VALUE = "WATT_VALUE";
	}
	
	private Context context;
	
	public TepcoMeterDao(Context c) {
		context = c;
	}

	public boolean registerWattInfo(WattDto watt) {
		boolean result = true;
		SQLiteDatabase db = null;
		try{
			TepcoMeterSQLiteOpenHelper dbOpenHelper = new TepcoMeterSQLiteOpenHelper(context);
			// DB接続
			db = dbOpenHelper.getWritableDatabase();
			db.beginTransaction();
			// 対象データが存在するか確認
			if (selectWattInfo(db, watt.getWattDay()) == null) {
				// 新規
				insertWattInfo(db, watt);
			} else {
				// 変更
				updateWattInfo(db, watt);
			}
			db.setTransactionSuccessful();
		} catch (ParseException e) {
			Log.e(TAG_NAME, e.getMessage(), e);
			result = false;
		} finally {
			if (db != null) {
				db.endTransaction();
				db.close();
			}
		}
		return result;
	}
	
	private void insertWattInfo(SQLiteDatabase db, WattDto watt) {
		String wattDay = WATT_DAY_FORMAT.format(watt.getWattDay());
		// WATT_INFO テーブル
		ContentValues values = new ContentValues();
		values.put(WattInfo.COL_WATT_DAY, wattDay);
		values.put(WattInfo.COL_CAPABILITY, watt.getCapability());
		values.put(WattInfo.COL_UPDATED, UPDATED_FORMAT.format(watt.getUpdated()));
		values.put(WattInfo.COL_INSERT_DATE, INSERT_DATE_FORMAT.format(Calendar.getInstance().getTime()));
		long ret = db.insert(WattInfo.TABLE_NAME, null, values);
		Log.d(TAG_NAME, WattInfo.TABLE_NAME + ":" + ret + ":" + watt.toString());
		// WATT_HOURS テーブル
		for (HourDto h : watt.getHours()) {
			values.clear();
			values.put(WattHours.COL_WATT_DAY, wattDay);
			values.put(WattHours.COL_WATT_HOUR, h.getWattHour());
			values.put(WattHours.COL_WATT_VALUE, h.getWattValue());
			db.insert(WattHours.TABLE_NAME, null, values);
		}
	}

	private void updateWattInfo(SQLiteDatabase db, WattDto watt) {
		// 削除
		String wattDay = WATT_DAY_FORMAT.format(watt.getWattDay());
		db.delete(WattInfo.TABLE_NAME, WattInfo.COL_WATT_DAY + "=?", new String[] {wattDay});
		db.delete(WattHours.TABLE_NAME, WattHours.COL_WATT_DAY + "=?", new String[] {wattDay});
		// 登録
		insertWattInfo(db, watt);
	}

	public WattDto getLatestWattInfo() {
		WattDto result = null;
		SQLiteDatabase db = null;
		try{
			TepcoMeterSQLiteOpenHelper dbOpenHelper = new TepcoMeterSQLiteOpenHelper(context);
			// DB接続
			db = dbOpenHelper.getReadableDatabase();
			// 取得
			Date latestDay = selectLatestDay(db);
			if (latestDay != null) {
				result = selectWattInfo(db, latestDay);
			}
		} catch (ParseException e) {
			Log.e(TAG_NAME, e.getMessage(), e);
		} finally {
			if (db != null) {
				db.close();
			}
		}
		return result;
	}

	/**
	 * @param db
	 * @return
	 * @throws ParseException 
	 */
	private Date selectLatestDay(SQLiteDatabase db) throws ParseException {
		Date result = null;
		Cursor cursor = null;
		try {
			// 検索
			cursor = db.query(
					WattInfo.TABLE_NAME
					,new String[] {WattInfo.COL_WATT_DAY}
					,null
					,null
					,null, null, WattInfo.COL_WATT_DAY + " DESC", "1");
			// 結果があれば詰め替え
			if (cursor.moveToNext()) {
				result = WATT_DAY_FORMAT.parse(cursor.getString(cursor.getColumnIndex(WattInfo.COL_WATT_DAY)));
				Log.d(TAG_NAME, "LastWattDay:" + result.toString());
			} else {
				Log.d(TAG_NAME, "結果なし");
			}
		} finally {
			if ((cursor != null) && !cursor.isClosed()) {
				cursor.close();
			}
		}
		return result;
	}

	/**
	 * @param time
	 */
	public WattDto getWattInfo(Date time) {
		WattDto result = null;
		SQLiteDatabase db = null;
		try{
			TepcoMeterSQLiteOpenHelper dbOpenHelper = new TepcoMeterSQLiteOpenHelper(context);
			// DB接続
			db = dbOpenHelper.getReadableDatabase();
			// 取得
			result = selectWattInfo(db, time);
		} catch (ParseException e) {
			Log.e(TAG_NAME, e.getMessage(), e);
		} finally {
			if (db != null) {
				db.close();
			}
		}
		return result;
	}

	private WattDto selectWattInfo(SQLiteDatabase db, Date wattDay) throws ParseException {
		WattDto result = null;
		Cursor cursor = null;
		try {
			// 検索
			cursor = db.query(
					WattInfo.TABLE_NAME
					,new String[] {WattInfo.COL_WATT_DAY, WattInfo.COL_CAPABILITY, WattInfo.COL_UPDATED, WattInfo.COL_INSERT_DATE}
					,WattInfo.COL_WATT_DAY + "=?"
					,new String[] {WATT_DAY_FORMAT.format(wattDay)}
					,null, null, null);
			// 結果があれば詰め替え
			if (cursor.moveToNext()) {
				result = new WattDto();
				result.setWattDay(WATT_DAY_FORMAT.parse(cursor.getString(cursor.getColumnIndex(WattInfo.COL_WATT_DAY))));
				result.setUpdated(UPDATED_FORMAT.parse(cursor.getString(cursor.getColumnIndex(WattInfo.COL_UPDATED))));
				result.setCapability(cursor.getInt(cursor.getColumnIndex(WattInfo.COL_CAPABILITY)));
				result.setHours(selectHours(db, wattDay));
				result.setInsertDate(INSERT_DATE_FORMAT.parse(cursor.getString(cursor.getColumnIndex(WattInfo.COL_INSERT_DATE))));
				Log.d(TAG_NAME, result.toString());
			} else {
				Log.d(TAG_NAME, "結果なし");
			}
		} finally {
			if ((cursor != null) && !cursor.isClosed()) {
				cursor.close();
			}
		}
		return result;
	}

	/**
	 * @param db
	 * @param time
	 * @return
	 */
	private List<HourDto> selectHours(SQLiteDatabase db, Date time) {
		List<HourDto> result = new ArrayList<HourDto>();
		Cursor cursor = null;
		try {
			// 検索
			cursor = db.query(
					WattHours.TABLE_NAME
					,new String[] {WattHours.COL_WATT_HOUR, WattHours.COL_WATT_VALUE}
					,WattInfo.COL_WATT_DAY + "=?"
					,new String[] {WATT_DAY_FORMAT.format(time)}
					,null, null, null);
			while (cursor.moveToNext()) {
				HourDto hourDto = new HourDto();
				hourDto.setWattHour(cursor.getInt(cursor.getColumnIndex(WattHours.COL_WATT_HOUR)));
				hourDto.setWattValue(cursor.getInt(cursor.getColumnIndex(WattHours.COL_WATT_VALUE)));
				result.add(hourDto);
			}
		} finally {
			if ((cursor != null) && !cursor.isClosed()) {
				cursor.close();
			}
		}
		return result;
	}

}
